package com.jhg.drgclinicalpathway.dao;


import com.jhg.drgclinicalpathway.domain.入径统计;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface CostAnalysisDao {

    @Select({
            "<script>",
            "SELECT TOP ${size} ${grp},",
            "COUNT(1) 病例数,",
            "SUM(总费用) 总费用,",
            "SUM(标准总费用) 标杆总费用,",
            "SUM(isnull(药品_主诊断金额,药品费)) 药品费 ,",
           " SUM(标准药品费) 标杆药品费,",
            "SUM(床日) 床日,",
           " SUM(CAST(标准床日 AS DECIMAL(10,2))) 标杆床日,",
            "SUM(isnull(检验_主诊断金额,检验费)) 检验费,",
            "SUM(标准检验费) 标杆检验费,",
            "SUM(isnull(检查_主诊断金额,检查费)) 检查费,",
            "SUM(标准检查费) 标杆检查费,",
            "SUM(isnull(耗材_主诊断金额,耗材费)) 耗材费,",
            "SUM(标准耗材费) 标杆耗材费,",
            "SUM(isnull(治疗_主诊断金额,治疗费)) 治疗费,",
            "SUM(标准治疗费) 标杆治疗费,",
            "SUM(isnull(手术_主诊断金额,手术费)) 手术费,",
            "SUM(标准手术费) 标杆手术费,权重 rw,",
            "AVG(ISNULL(权重,0)) CMI,",
            "SUM(CASE WHEN 是否评估=1 THEN 1 ELSE 0 END) 评价例数,",
            "SUM(ISNULL(得分,0)) 得分,",
            "SUM(CASE WHEN 得分 IS NULL THEN 0 ELSE 1 END) 评分人次,",
            "AVG(ISNULL(得分,0)) 平均得分,",
           " AVG(总费用/标准总费用) 费用消耗,",
            "AVG(床日/CONVERT(NUMERIC(8,2),标准床日)) 时间消耗,",
            "SUM(CASE WHEN 病组编码 IS NULL THEN 0 ELSE 1 END) 入组数",
//            "SUM(CAST(ISNULL(是否评估,0) AS INT)) 评价例数",
//            "SUM(ISNULL(权重,0)) 总权重",
            "FROM VDCP_病人信息_路径指标参考",
            "WHERE 1=1",
            "AND ${t} BETWEEN #{stime} AND #{etime} AND 是否入径=1",
            "AND ${w} ",
            "GROUP BY 权重,${grp}",
            "ORDER BY ${order}",
            "</script>"

    })
    public List<Map<String,Object>> ls(@Param("grp") String grp,
                                       @Param("stime") String stime,
                                       @Param("etime") String etime,
                                       @Param("order") String order,
                                       @Param("w") String w,
                                       @Param("size") Integer size,
                                       @Param("t") String t
                                       );

    @Select({
            "select c.模板名称 name,",
            "SUM(CAST(ISNULL(a.路径入径,0) AS int)) grp,COUNT(*) count",
            ",SUM(CAST(ISNULL(a.路径完成,0) AS int)) finish",
            ",SUM(床日) bed,SUM(总费用) cost",
            ",avg(ISNULL(a.医嘱符合度,100)) fhd",
            ",SUM(CAST(ISNULL(a.[三十天内重复住院],0) AS int)) zcry",
            ",SUM(CAST(ISNULL(a.是否变异,0) as int)) sfby",
            "  FROM dbo.VDCP_病人信息 a ",
            "left join V病组标杆值 b on a.病组编码=b.分组编码 ",
            "LEFT JOIN V路径模板2路径版本 c on a.版本ID=c.模板ID ",
            "WHERE 1=1 AND 出院日期 BETWEEN #{stime} AND #{etime} AND 是否评估=1 ",
            "and c.模板ID is not null",
            "group by c.模板名称"
    })
    public List<入径统计> 入径统计By时间(@Param("stime") String stime,
                               @Param("etime") String etime);

    @Select({
            "select 科室 ks,",
            "SUM(CAST(ISNULL(a.路径入径,0) AS int)) grp,COUNT(*) count",
            ",SUM(CAST(ISNULL(a.路径完成,0) AS int)) finish",
            ",SUM(床日) bed,SUM(总费用) cost",
            ",avg(ISNULL(a.医嘱符合度,100)) fhd",
            ",SUM(CAST(ISNULL(a.[三十天内重复住院],0) AS int)) zcry",
            ",SUM(CAST(ISNULL(a.是否变异,0) as int)) sfby",
            "  FROM dbo.VDCP_病人信息 a ",
            "left join V病组标杆值 b on a.病组编码=b.分组编码 ",
            "LEFT JOIN V路径模板2路径版本 c on a.版本ID=c.模板ID ",
            "WHERE 1=1 AND 出院日期 BETWEEN #{stime} AND #{etime} AND 是否评估=1 ",
            "and c.模板ID is not null",
            "group by a.科室"
    })
    public List<入径统计> 科室入组统计(@Param("stime") String stime,@Param("etime") String etime);

    @Select({
            "select STUFF(CONVERT(varchar(30),出院日期,112),7,100,'') name, ",
            "SUM(CAST(ISNULL(a.路径入径,0) AS int)) grp,COUNT(*) count",
            ",SUM(CAST(ISNULL(a.路径完成,0) AS int)) finish",
            ",SUM(床日) bed,SUM(总费用) cost",
            ",avg(ISNULL(a.医嘱符合度,100)) fhd",
            ",SUM(CAST(ISNULL(a.[三十天内重复住院],0) AS int)) zcry",
            ",SUM(CAST(ISNULL(a.是否变异,0) as int)) sfby",
            "  FROM dbo.VDCP_病人信息 a ",
            "left join V病组标杆值 b on a.病组编码=b.分组编码 ",
            "LEFT JOIN V路径模板2路径版本 c on a.版本ID=c.模板ID ",
            "WHERE 1=1 AND 出院日期 BETWEEN #{stime} AND #{etime} AND 是否评估=1 ",
            "and c.模板ID is not null",
            "group by STUFF(CONVERT(varchar(30),出院日期,112),7,100,'')"
    })
    public List<入径统计> 月度统计(@Param("stime") String stime,@Param("etime") String etime);

    @Select({
            "<script>",
            "EXEC P_获取项目Top @日期类型=#{t},@开始日期=#{stime}," +
                    "@结束日期=#{etime},@科室=#{ks},@医生=#{doc},@病组编码=#{c},@项目类型=#{pt},@限制数目=#{ts},@路径ID=#{ljid}",
            "</script>"
    })
    public List<Map<String,Object>> 获取项目Top(@Param("stime") String stime,
                                            @Param("etime") String etime,
                                            @Param("ks") String ks,
                                            @Param("doc") String doc,
                                            @Param("c") String c,
                                            @Param("t") String t,
                                            @Param("pt") String pt,
                                            @Param("ts") Integer ts,
                                            @Param("ljid") Integer ljid
                                            );
//
    @Select({
            "<script>",
            "EXEC PRO_病人路径评估_项目对比增删 @操作方式=#{操作方式},@版本ID=#{版本ID},@项目ID=#{项目ID}",
            "</script>"
    })
    public void PRO_病人路径评估_项目对比增删(@Param("操作方式") String 操作方式,
                                  @Param("版本ID") String 版本ID,
                                  @Param("项目ID") String 项目ID
                                  );
}
